﻿using System;
using System.Collections;
using System.Data;
using System.Drawing.Printing;
using System.Reflection;
using C1.Win.C1Preview;
using C1.C1Report;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Drawing;
using System.Diagnostics;
using System.Collections.Generic;

namespace PCSUtils.Utils
{
    public class ExecuteReport : MarshalByRefObject, IDynamicReport
    {
        public ExecuteReport()
        {

        }
        private string mConnectionString;
        private ReportBuilder mReportBuilder;
        private C1PrintPreviewControl mReportViewer;
        private object mResult;
        private string mLayoutFile;
        private readonly string mstrReportDefFolder = System.Windows.Forms.Application.StartupPath + "\\" +
                                                     PCSComUtils.Common.Constants.REPORT_DEFINITION_STORE_LOCATION;


        /// <summary>
        /// ConnectionString, provide for the Dynamic Report
        /// ALlow Dynamic Report to access the DataBase of PCS
        /// </summary>
        public string PCSConnectionString
        {
            get { return mConnectionString; }
            set { mConnectionString = value; }
        }

        /// <summary>
        /// Report Builder Utility Object
        /// Dynamic Report can use this object to render, modify, layout the report
        /// </summary>
        public ReportBuilder PCSReportBuilder
        {
            get { return mReportBuilder; }
            set { mReportBuilder = value; }
        }

        /// <summary>
        /// ReportViewer Object, provide for the DynamicReport, 
        /// allow Dynamic Report to manipulate with the REportViewer, 
        /// modify the report after rendered if needed
        /// </summary>
        public C1PrintPreviewControl PCSReportViewer
        {
            get { return mReportViewer; }
            set { mReportViewer = value; }
        }
        private string mReportFolder = string.Empty;
        public string ReportDefinitionFolder
        {
            get { return mReportFolder; }
            set { mReportFolder = value; }
        }

        /// <summary>
        /// Store other result if any. Ussually we store return DataTable here to display on the ReportViewer Form's Grid
        /// </summary>
        public object Result
        {
            get { return mResult; }
            set { mResult = value; }
        }

        private bool mUseReportViewerRenderEngine = true;

        /// <summary>
        /// Notify PCS whether the rendering report process is run by
        /// this IDynamicReport or the ReportViewer Engine (in the ReportViewer form)
        /// </summary>
        public bool UseReportViewerRenderEngine
        {
            get { return mUseReportViewerRenderEngine; }
            set { mUseReportViewerRenderEngine = value; }
        }

        private string mstrReportDefinitionFolder = string.Empty;
        /// <summary>
        /// Inform External Process where to find out the ReportLayout	 ( the PCS' ReportDefinition Folder Path )
        /// </summary>				



        private string mstrReportLayoutFile = string.Empty;
        /// <summary>
        /// Inform External Process about the Layout file
        /// in which PCS instruct to use
        /// (PCS will assign this property while ReportViewer Form execute,
        /// ReportVIewer form will use the layout file in the report config entry to put in this property)
        /// </summary>		
        public string ReportLayoutFile
        {
            get
            {
                return mstrReportLayoutFile;
            }
            set
            {
                mstrReportLayoutFile = value;
            }
        }


        public object Invoke(string pstrMethod, object[] pobjParameters)
        {
            return this.GetType().InvokeMember(pstrMethod, BindingFlags.InvokeMethod, null, this, pobjParameters);
        }

        public void showReport(DataSet dsData, string pLayOutFile, List<object> listParams, List<object> listValues)
        {

            C1Report rptReport = new C1Report();
            mLayoutFile = pLayOutFile;

            try
            {

                string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mstrReportDefFolder + "\\" + mLayoutFile);
                rptReport.Load(mstrReportDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);
                if (listValues != null && listParams != null && listParams.Count > 0 && listValues.Count == listParams.Count)
                {
                    for (int i = 0; i < listParams.Count; i++)
                    {
                        rptReport.Fields[listParams[i].ToString()].Value = listValues[i].ToString();
                    }
                }
                rptReport.Layout.PaperSize = PaperKind.A4;
                // set datasource object that provides data to report.
                rptReport.DataSource.Recordset = dsData.Tables[0];
                // render report
                rptReport.Render();

                C1.Win.C1Preview.C1PrintPreviewDialog ppvViewer = new C1.Win.C1Preview.C1PrintPreviewDialog();
                ppvViewer.PreviewPane.ZoomMode = ZoomModeEnum.ActualSize;
                ppvViewer.NavigationPanelVisible = false;
                ppvViewer.Document = rptReport.Document;
                ppvViewer.Show();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void ShowReportSub(DataSet dsData, string pLayOutFile)
        {
            C1Report rptReport = new C1Report();
            C1Report rptReport1 = new C1Report();
            ReportWithSubReportBuilder reportBuilder = new ReportWithSubReportBuilder();
            mLayoutFile = pLayOutFile;

            try
            {

                string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mstrReportDefFolder + "\\" + mLayoutFile);
                string strPathFile = mstrReportDefFolder + "\\" + mLayoutFile;
                rptReport.Load(mstrReportDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);

                rptReport.Layout.PaperSize = PaperKind.A4;
                string str = "";
                C1.Win.C1Preview.C1PrintPreviewDialog ppvViewer = new C1.Win.C1Preview.C1PrintPreviewDialog();
                rptReport.DataSource.Recordset = dsData.Tables[0];
                rptReport.Fields["SubReport"].Subreport.Load(strPathFile, "SubReport");
                rptReport.Fields["SubReport"].Subreport.DataSource.Recordset = dsData.Tables[0];
                rptReport.Fields["SubReport"].Subreport.Fields["SubReport_Lien3"].Subreport.Load(strPathFile, "SubReport_Lien3");
                rptReport.Fields["SubReport"].Subreport.Fields["SubReport_Lien3"].Subreport.DataSource.Recordset = dsData.Tables[0];
                ppvViewer.NavigationPanelVisible = false;
                ppvViewer.Document = rptReport.Document;
                ppvViewer.PreviewPane.ZoomMode = C1.Win.C1Preview.ZoomModeEnum.ActualSize;
                ppvViewer.Show();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void showReportImagExcel(DataSet dsData, string pLayOutFile, string strFileExcel, int iStartRow, int iStartCol, string strColExel1, string strColExcel2, string strChartName)
        {
            C1Report rptReport = new C1Report();
            mLayoutFile = pLayOutFile;

            string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mstrReportDefFolder + "\\" + mLayoutFile);
            rptReport.Load(mstrReportDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);

            rptReport.Layout.PaperSize = PaperKind.A4;
            Field fldChart = rptReport.Fields["fldChart"];
            string EXCEL_FILE = "Template_BaocaoTheodoidienbiengia.xls";

            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

            /// Copy layout excel report file to ExcelReport folder with a UTC datetime name
            File.Copy(strTemplateFilePath, strDestinationFilePath, true);
            ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);

            try
            {

                for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                {   //strColExel1
                    objXLS.GetCell(iStartRow + i, iStartCol).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i][strColExel1].ToString());
                    objXLS.GetCell(iStartRow + i, iStartCol + 1).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i][strColExcel2].ToString());
                }


                // hoan thanh

                ChartObject chart = objXLS.GetChart(strChartName);
                chart.Chart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap, XlPictureAppearance.xlScreen);
                Image image = (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
                fldChart.Visible = true;
                fldChart.Text = "";
                fldChart.Picture = image;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                objXLS.CloseWorkbook();
                objXLS.Dispose();
                objXLS = null;

                #endregion
            }

            // set datasource object that provides data to report.
            rptReport.DataSource.Recordset = dsData.Tables[0];
            // render report
            rptReport.Render();

            C1.Win.C1Preview.C1PrintPreviewDialog ppvViewer = new C1.Win.C1Preview.C1PrintPreviewDialog();
            ppvViewer.PreviewPane.ZoomMode = C1.Win.C1Preview.ZoomModeEnum.ActualSize;
            ppvViewer.NavigationPanelVisible = false;
            ppvViewer.Document = rptReport.Document;
            //ppvViewer.ReportFile = mLayoutFile;
            ppvViewer.Show();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dsData"></param>
        /// <param name="pLayOutFile"></param>
        /// <param name="strFileExcel"></param>
        /// <param name="iStartRow"></param>
        /// <param name="iStartCol"></param>
        /// <param name="strColExel1"></param>
        /// <param name="strColExcel2"></param>
        /// <param name="strChartName"></param>
        /// <param name="iCount"></param>
        public void showReportImagExcel(DataSet dsData, string pLayOutFile, string strFileExcel, int iStartRow, int iStartCol, string strColExel1, string strColExcel2, string strColExcel3, string strChartName)
        {
            C1Report rptReport = new C1Report();
            mLayoutFile = pLayOutFile;

            string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mstrReportDefFolder + "\\" + mLayoutFile);
            rptReport.Load(mstrReportDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);

            rptReport.Layout.PaperSize = PaperKind.A4;
            Field fldChart = rptReport.Fields["fldChart"];
            string EXCEL_FILE = "Template_BieuDososanhCungKy.xls";

            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

            /// Copy layout excel report file to ExcelReport folder with a UTC datetime name
            File.Copy(strTemplateFilePath, strDestinationFilePath, true);
            ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);

            try
            {

                for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                {   //strColExel1
                    objXLS.GetCell(iStartRow + i, iStartCol).Value2 = Convert.ToInt32(dsData.Tables[0].Rows[i][strColExel1].ToString());
                    objXLS.GetCell(iStartRow + i, iStartCol + 1).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i][strColExcel2].ToString());
                    objXLS.GetCell(iStartRow + i, iStartCol + 2).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i][strColExcel3].ToString());
                    //strColExcel3
                }

                // hoan thanh

                ChartObject chart = objXLS.GetChart(strChartName);
                chart.Chart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap, XlPictureAppearance.xlScreen);
                Image image = (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
                fldChart.Visible = true;
                fldChart.Text = "";
                fldChart.Picture = image;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                objXLS.CloseWorkbook();
                objXLS.Dispose();
                objXLS = null;

                #endregion
            }

            // set datasource object that provides data to report.
            rptReport.DataSource.Recordset = dsData.Tables[0];
            // render report
            rptReport.Render();

            C1.Win.C1Preview.C1PrintPreviewDialog ppvViewer = new C1.Win.C1Preview.C1PrintPreviewDialog();
            ppvViewer.PreviewPane.ZoomMode = C1.Win.C1Preview.ZoomModeEnum.ActualSize;
            ppvViewer.NavigationPanelVisible = false;
            ppvViewer.Document = rptReport.Document;
            ppvViewer.Show();
        }

        public void showReportImageExcelRevenue(DataSet dsData, string pLayOutFile, string strFileExcel, int iStartRow, int iStartCol, string strColExel1, string strColExcel2, string strColExcel3, string strColExcel4, string strChartName)
        {
            C1Report rptReport = new C1Report();
            mLayoutFile = pLayOutFile;

            string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mstrReportDefFolder + "\\" + mLayoutFile);
            rptReport.Load(mstrReportDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);

            rptReport.Layout.PaperSize = PaperKind.A4;
            Field fldChart = rptReport.Fields["fldChart"];
            string EXCEL_FILE = strFileExcel;

            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

            /// Copy layout excel report file to ExcelReport folder with a UTC datetime name
            File.Copy(strTemplateFilePath, strDestinationFilePath, true);
            ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);

            try
            {

                for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                {   //strColExel1
                    try
                    {
                        //Cot Thang
                        objXLS.GetCell(iStartRow + i, iStartCol).Value2 = Convert.ToInt32(dsData.Tables[0].Rows[i][strColExel1].ToString());
                    }
                    catch
                    {

                    }

                    // Cot Price1
                    try
                    {
                        objXLS.GetCell(iStartRow + i, iStartCol + 1).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i][strColExcel2].ToString());
                        //objXLS.GetCell(iStartRow + i, iStartCol + 1).Value2 = i;
                    }
                    catch
                    { }

                    //Cot Price2
                    try
                    {
                        objXLS.GetCell(iStartRow + i, iStartCol + 2).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i][strColExcel3].ToString());
                    }
                    catch
                    { }

                    ///Cot Price 3
                    try
                    {
                        objXLS.GetCell(iStartRow + i, iStartCol + 3).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i][strColExcel4].ToString());
                    }
                    catch
                    { }
                    //strColExcel3
                }

                // hoan thanh

                ChartObject chart = objXLS.GetChart(strChartName);
                chart.Chart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap, XlPictureAppearance.xlScreen);
                Image image = (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
                fldChart.Visible = true;
                fldChart.Text = "";
                fldChart.Picture = image;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                objXLS.CloseWorkbook();
                objXLS.Dispose();
                objXLS = null;

                #endregion
            }

            // set datasource object that provides data to report.
            rptReport.DataSource.Recordset = dsData.Tables[0];
            // render report
            rptReport.Render();

            C1.Win.C1Preview.C1PrintPreviewDialog ppvViewer = new C1.Win.C1Preview.C1PrintPreviewDialog();
            ppvViewer.PreviewPane.ZoomMode = C1.Win.C1Preview.ZoomModeEnum.ActualSize;
            ppvViewer.NavigationPanelVisible = false;
            ppvViewer.Document = rptReport.Document;
            ppvViewer.Show();
        }


        public void ExportExcel(DataSet dsData, string strFileExcel, int iStartRow, int iStartCol)
        {

            string EXCEL_FILE = strFileExcel;

            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;
            //FormControlComponents.NowToUTCString() 
            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + DateTime.Today.ToString("dd/MM/yyyy").Replace("/", "") + ".XLS";

            /// Copy layout excel report file to ExcelReport folder with a UTC datetime name
            File.Copy(strTemplateFilePath, strDestinationFilePath, true);
            ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);

            try
            {

                for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                {   //strColExel1
                    objXLS.GetCell(iStartRow + i, iStartCol).Value2 = Convert.ToDecimal(i.ToString());
                    objXLS.GetCell(iStartRow + i, iStartCol + 1).Value2 = dsData.Tables[0].Rows[i]["Product_Name"].ToString();
                    objXLS.GetCell(iStartRow + i, iStartCol + 2).Value2 = dsData.Tables[0].Rows[i]["Code"].ToString();

                    objXLS.GetCell(iStartRow + i, iStartCol + 3).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i]["Qty"].ToString());
                    objXLS.GetCell(iStartRow + i, iStartCol + 8).Value2 = Convert.ToDecimal(dsData.Tables[0].Rows[i]["Price"].ToString());
                    try
                    {
                        objXLS.GetCell(iStartRow + i, iStartCol + 9).Value2 = (Convert.ToDecimal(dsData.Tables[0].Rows[i]["Price"].ToString())) * (Convert.ToDecimal(dsData.Tables[0].Rows[i]["Qty"].ToString()));
                    }
                    catch
                    { }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                objXLS.CloseWorkbook();
                objXLS.Dispose();
                objXLS = null;

                #endregion
            }

        }
        public void ShowReportChart(DataSet dsData, string pLayOutFile, string strFileExcel, int iStartRow, int iStartCol, string strChartName)
        {
            C1Report rptReport = new C1Report();
            mLayoutFile = pLayOutFile;

            string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mstrReportDefFolder + "\\" + mLayoutFile);
            rptReport.Load(mstrReportDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);

            rptReport.Layout.PaperSize = PaperKind.A4;
            Field fldChart = rptReport.Fields["fldChart"];
            string EXCEL_FILE = strFileExcel;

            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

            /// Copy layout excel report file to ExcelReport folder with a UTC datetime name
            File.Copy(strTemplateFilePath, strDestinationFilePath, true);
            ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);

            try
            {

                for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                {   //strColExel1
                    try
                    {
                        //Cot Thang
                        objXLS.GetCell(iStartRow + i, iStartCol).Value2 = Decimal.Parse(dsData.Tables[0].Rows[i]["Actual"].ToString());
                    }
                    catch (Exception ex)
                    { }
                }

                // hoan thanh

                ChartObject chart = objXLS.GetChart(strChartName);
                chart.Chart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap, XlPictureAppearance.xlScreen);
                Image image = (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
                fldChart.Visible = true;
                fldChart.Text = "";
                fldChart.Picture = image;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                objXLS.CloseWorkbook();
                objXLS.Dispose();
                objXLS = null;

                #endregion
            }

            // set datasource object that provides data to report.
            rptReport.DataSource.Recordset = dsData.Tables[0];
            // render report
            rptReport.Render();

            C1.Win.C1Preview.C1PrintPreviewDialog ppvViewer = new C1.Win.C1Preview.C1PrintPreviewDialog();
            ppvViewer.PreviewPane.ZoomMode = C1.Win.C1Preview.ZoomModeEnum.ActualSize;
            ppvViewer.NavigationPanelVisible = false;
            ppvViewer.Document = rptReport.Document;
            ppvViewer.Show();
        }

        public void ExportExcelSaleOrder(DataSet dsData, string strFileExcel, int iStartRow, int iStartCol, DateTime dDate)
        {
            try
            {
                #region Report layout

                string EXCEL_FILE = strFileExcel;

                string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;
                //FormControlComponents.NowToUTCString() 
                string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + DateTime.Today.ToString("dd/MM/yyyy").Replace("/", "") + ".XLS";

                /// Copy layout excel report file to ExcelReport folder with a UTC datetime name

                File.Copy(strTemplateFilePath, strDestinationFilePath, true);
                ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);

                try
                {
                    objXLS.GetCell(4, 1).Value2 = "Tháng " + dDate.ToString("MM") + " năm " + dDate.ToString("yyyy");
                    for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                    {   //strColExel1
                        //objXLS.GetCell(iStartRow + i, iStartCol).Value2 = Convert.ToDecimal(i.ToString());
                        objXLS.GetCell(iStartRow + i, iStartCol).Value2 = dsData.Tables[0].Rows[i]["TransDate"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 1).Value2 = dsData.Tables[0].Rows[i]["tongyc"];

                        objXLS.GetCell(iStartRow + i, iStartCol + 3).Value2 = dsData.Tables[0].Rows[i]["SLDAPUNG_XHVC"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 4).Value2 = dsData.Tables[0].Rows[i]["SLDAPUNG_KHVC"];

                        objXLS.GetCell(iStartRow + i, iStartCol + 5).Value2 = dsData.Tables[0].Rows[i]["SLKO_DAPUNG"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 9).Value2 = dsData.Tables[0].Rows[i]["DATHANG"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 10).Value2 = dsData.Tables[0].Rows[i]["Code"];


                    }


                #endregion

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                    objXLS.CloseWorkbook();
                    objXLS.Dispose();
                    objXLS = null;

                    #endregion
                }
                Process.Start(strDestinationFilePath);
            }
            catch
            {
                MessageBox.Show("File dang duoc mo");
            }
        }

        public void ExportExcelSaleOrderByDay(DataSet dsData, string strFileExcel, int iStartRow, int iStartCol, DateTime dDate)
        {
            try
            {
                #region Report layout

                string EXCEL_FILE = strFileExcel;

                string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;
                //FormControlComponents.NowToUTCString() 
                string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + DateTime.Today.ToString("dd/MM/yyyy").Replace("/", "") + ".XLS";

                /// Copy layout excel report file to ExcelReport folder with a UTC datetime name

                File.Copy(strTemplateFilePath, strDestinationFilePath, true);
                ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);

                try
                {
                    //objXLS.GetCell(4, 1).Value2 = "Tháng " + dDate.ToString("MM") + " năm " + dDate.ToString("yyyy");
                    for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                    {   //strColExel1
                        //objXLS.GetCell(iStartRow + i, iStartCol).Value2 = Convert.ToDecimal(i.ToString());
                        objXLS.GetCell(iStartRow + i, iStartCol).Value2 = dsData.Tables[0].Rows[i]["TransDate"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 1).Value2 = dsData.Tables[0].Rows[i]["TENKH"];

                        objXLS.GetCell(iStartRow + i, iStartCol + 2).Value2 = dsData.Tables[0].Rows[i]["TenSP"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 3).Value2 = dsData.Tables[0].Rows[i]["OrderQuantity"];

                        objXLS.GetCell(iStartRow + i, iStartCol + 4).Value2 = dsData.Tables[0].Rows[i]["DELIQty"];
                        try
                        {
                            double dOrderQuantity = 0;
                            double dDELIQty = 0;
                            try
                            {
                                dOrderQuantity = Convert.ToDouble(dsData.Tables[0].Rows[i]["OrderQuantity"].ToString());
                            }
                            catch
                            { }

                            try
                            {
                                dDELIQty = Convert.ToDouble(dsData.Tables[0].Rows[i]["DELIQty"].ToString());
                            }
                            catch
                            { }

                            objXLS.GetCell(iStartRow + i, iStartCol + 5).Value2 = dOrderQuantity - dDELIQty;
                        }
                        catch
                        {
                        }
                        objXLS.GetCell(iStartRow + i, iStartCol + 6).Value2 = dsData.Tables[0].Rows[i]["RequiredDate"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 7).Value2 = dsData.Tables[0].Rows[i]["DueDate"];

                        objXLS.GetCell(iStartRow + i, iStartCol + 8).Value2 = dsData.Tables[0].Rows[i]["Carrier"];
                        objXLS.GetCell(iStartRow + i, iStartCol + 9
                            ).Value2 = dsData.Tables[0].Rows[i]["Comment"];

                        try
                        {
                            double dThanhtien = 0;
                            double dOrderQuantity = 0;
                            double dPrice = 0;
                            try
                            {
                                dOrderQuantity = Convert.ToDouble(dsData.Tables[0].Rows[i]["OrderQuantity"].ToString());
                            }
                            catch
                            {
                            }
                            try
                            {
                                dPrice = Convert.ToDouble(dsData.Tables[0].Rows[i]["Unitprice"].ToString());
                            }
                            catch
                            {
                            }
                            dThanhtien = dOrderQuantity * dPrice;
                            objXLS.GetCell(iStartRow + i, iStartCol + 10).Value2 = dThanhtien;

                        }
                        catch
                        { }


                    }


                #endregion

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                    objXLS.CloseWorkbook();
                    objXLS.Dispose();
                    objXLS = null;

                    #endregion
                }
                Process.Start(strDestinationFilePath);
            }
            catch
            {
                MessageBox.Show("File dang duoc mo");
            }
        }
        public void ExportExcelKeHoachSanXuat(DataSet dsData, string strFileExcel, ArrayList arrList, ArrayList arrListDate, int iStartRow, int iStartCol)
        {



            string EXCEL_FILE = strFileExcel;

            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

            //FormControlComponents.NowToUTCString() 
            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + DateTime.Today.ToString("dd/MM/yyyy").Replace("/", "") + ".XLS";

            /// Copy layout excel report file to ExcelReport folder with a UTC datetime name
            /// 
            try
            {
                File.Copy(strTemplateFilePath, strDestinationFilePath, true);
            }
            catch
            {
                MessageBox.Show("File đang mở xin vui lòng đóng lại,rồi in lại báo cáo");
                return;
            }
            ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);
            int iRow = 0;
            int iIndex = 0;
            int iCol = 0;
            Hashtable hscol = new Hashtable();
            try
            {
                double doanhthutong = 0;
                double dCongNo = 0;
                objXLS.GetCell(iStartRow, iStartCol).Value2 = "LINE";
                objXLS.GetCell(iStartRow, iStartCol + 1).Value2 = "NUMBER";
                objXLS.GetCell(iStartRow, iStartCol + 2).Value2 = "NAME";
                objXLS.GetCell(iStartRow, iStartCol + 3).Value2 = "DATE";

                if (arrListDate.ToArray().Length > 0)
                {
                    int idem = 1;
                    foreach (string strDate in arrListDate)
                    {
                        objXLS.GetCell(iStartRow, iStartCol + 3 + idem).Value2 =strDate;
                        idem++;
                    }
                }
                iStartRow = iStartRow + 1;
                //iRow = iStartRow;
                #region View infor product
                if (arrList.ToArray().Length > 0)
                {
                    foreach (string iProductID in arrList)
                    {
                        int iRowGroup = 0;
                        for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                        {

                            if (dsData.Tables[0].Rows[i]["ProductID"].ToString().Trim() == iProductID.ToString().Trim())
                            {
                                //iRow++;
                                iIndex++;
                                iRowGroup++;
                                int iCurRow = iRow;
                                iCurRow = iCurRow;
                                //objXLS.GetCell(iStartRow + iCurRow, iStartCol).Value2 = iIndex;
                                objXLS.GetCell(iStartRow + iCurRow, iStartCol).Value2 = dsData.Tables[0].Rows[i]["LCode"];

                                try
                                {
                                    objXLS.GetCell(iStartRow + iCurRow, iStartCol + 1).Value2 = dsData.Tables[0].Rows[i]["ProductCode"].ToString().Trim();
                                }
                                catch
                                { }
                                objXLS.GetCell(iStartRow + iCurRow, iStartCol + 2).Value2 = dsData.Tables[0].Rows[i]["ProductName"];
                                objXLS.GetCell(iStartRow + iCurRow, iStartCol + 3).Value2 = "SYSTEM PLAN";
                                //objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = dsData.Tables[0].Rows[i]["DemandQuantity"];
                                iCurRow++;
                                iRow++;
                                //objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = dsData.Tables[0].Rows[i]["Quantity"];
                                objXLS.GetCell(iStartRow + iCurRow, iStartCol + 3).Value2 = "ACTUAL PLAN";
                                iCurRow++;
                                iRow++;
                                //objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = dsData.Tables[0].Rows[i]["NetAvailableQuantity"];
                                objXLS.GetCell(iStartRow + iCurRow, iStartCol + 3).Value2 = "STOCK";
                                iCurRow++;
                                iRow++;
                                hscol.Add(iProductID, iRow-iStartRow);
                                break;
                            }
                        }
                    }
                }

                #endregion
                iRow = 0;
                iCol = 0;
                #region View so luong theo ngay
                if (arrListDate.ToArray().Length > 0)
                {
                    
                    foreach (string strDate in arrListDate)
                    {
                        iRow = 0;
                        
                        if (arrList.ToArray().Length > 0)
                        {

                            foreach (string iProductID in arrList)
                            {
                                for (int i = 0; i < dsData.Tables[0].Rows.Count; i++)
                                {   //strColExel1

                                    if (dsData.Tables[0].Rows[i]["ProductID"].ToString().Trim() == iProductID.ToString().Trim())
                                    {
                                        
                                        if (Convert.ToDateTime(dsData.Tables[0].Rows[i]["DueDate"]).ToString("dd/MM/yyyy") == strDate)
                                        {
                                            //iRow++;
                                            if (hscol != null)
                                            {
                                                IDictionaryEnumerator en = hscol.GetEnumerator();
                                                while (en.MoveNext())
                                                {
                                                    if (en.Key.ToString() == iProductID)
                                                    {
                                                        iRow = Convert.ToInt32(en.Value.ToString());
                                                    }

                                                }

                                            }
                                            int iCurRow = iRow;
                                            objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = dsData.Tables[0].Rows[i]["DemandQuantity"];
                                            iCurRow++;
                                            iRow++;
                                            objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = dsData.Tables[0].Rows[i]["Quantity"];

                                            iCurRow++;
                                            iRow++;
                                            objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = dsData.Tables[0].Rows[i]["NetAvailableQuantity"];
                                            iCurRow++;
                                            iRow++;
                                            //break;
                                        }
                                        /*else
                                        {
                                            objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = null;
                                            iCurRow++;
                                            iRow++;
                                            objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = null;

                                            iCurRow++;
                                            iRow++;
                                            objXLS.GetCell(iStartRow + iCurRow, iStartCol + 4 + iCol).Value2 = null;
                                            iCurRow++;
                                            iRow++;
                                           // break;
                                        }*/
                                    }
                                }
                            }
                            //Next day
                            iCol++;
                        }
                    }
                }
                #endregion

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                objXLS.CloseWorkbook();
                objXLS.Dispose();
                objXLS = null;
                Process.Start(strDestinationFilePath);
                #endregion
            }

        }


        public void ExportExcelBaoGiaKeHoach(System.Data.DataTable dsData, string strFileExcel, int iStartRow, int iStartCol)
        {
            string EXCEL_FILE = strFileExcel;
            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;
            //FormControlComponents.NowToUTCString() 
            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + DateTime.Today.ToString("dd/MM/yyyy").Replace("/", "") + ".XLS";
            /// Copy layout excel report file to ExcelReport folder with a UTC datetime name
            /// 
            try
            {
                File.Copy(strTemplateFilePath, strDestinationFilePath, true);
            }
            catch
            {
                MessageBox.Show("File đang mở xin vui lòng đóng lại,rồi in lại báo cáo");
                return;
            }
            ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);
            int iRow = 0;
            int iIndex = 0;
            int iCol = 0;
            Hashtable hscol = new Hashtable();
            try
            {
                if (dsData!=null)
                {
                    int idem = 0;
                    objXLS.GetCell(1, iStartCol +11).Value2 = DateTime.Now.ToString("dd/MM/yyyy");
                    foreach (DataRow dr in dsData.Rows)
                    {
                        objXLS.GetCell(iStartRow + idem, iStartCol).Value2 = dr["Numberedlist"];
                        objXLS.GetCell(iStartRow + idem, iStartCol + 2).Value2 = dr["ProcessCode"];
                        objXLS.GetCell(iStartRow + idem, iStartCol + 3).Value2 = dr["Part No."];
                        objXLS.GetCell(iStartRow + idem, iStartCol + 4).Value2 = dr["ProcessName"];
                        
                        objXLS.GetCell(iStartRow + idem, iStartCol + 5).Value2 = dr["Part Name"];
                        double dMaterialExpense=0;
                        double dOutSideExpense=0;
                        double dCostPerMin=0;
                        if (dr["MaterialExpense"] != DBNull.Value)
                        {
                            dMaterialExpense = Convert.ToDouble(dr["MaterialExpense"]);
                        }
                        if (dr["OutSideExpense"] != DBNull.Value)
                        {
                            dOutSideExpense = Convert.ToDouble(dr["OutSideExpense"]);
                        }
                        if (dr["CostPerMin"] != DBNull.Value)
                        {
                            dCostPerMin = Convert.ToDouble(dr["CostPerMin"]);
                        }
                        objXLS.GetCell(iStartRow + idem, iStartCol + 6).Value2 = dr["MaterialExpense"];
                        objXLS.GetCell(iStartRow + idem, iStartCol + 7).Value2 = dr["OutSideExpense"];
                        objXLS.GetCell(iStartRow + idem, iStartCol + 9).Value2 = dr["CostPerMin"];
                        idem++;
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                #region SAVE, CLOSE EXCEL FILE CONTAIN REPORT

                objXLS.CloseWorkbook();
                objXLS.Dispose();
                objXLS = null;
                Process.Start(strDestinationFilePath);
                #endregion
            }

        }

        public void showReport(System.Data.DataTable dsData, string pLayOutFile)
        {

            
            C1Report rptReport = new C1Report();
            mLayoutFile = pLayOutFile;

            string[] arrstrReportInDefinitionFile = rptReport.GetReportInfo(mstrReportDefFolder + "\\" + mLayoutFile);
            rptReport.Load(mstrReportDefFolder + "\\" + mLayoutFile, arrstrReportInDefinitionFile[0]);

            rptReport.Layout.PaperSize = PaperKind.A4;
            Field fldChart = rptReport.Fields["fldChart"];
            string EXCEL_FILE = "Template_BieuDososanhCungKy.xls";

            string strTemplateFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

            string strDestinationFilePath = mstrReportDefFolder + Path.DirectorySeparatorChar + Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

            

            // set datasource object that provides data to report.
            rptReport.DataSource.Recordset = dsData;
            // render report
            rptReport.Render();

            C1.Win.C1Preview.C1PrintPreviewDialog ppvViewer = new C1.Win.C1Preview.C1PrintPreviewDialog();
            ppvViewer.PreviewPane.ZoomMode = C1.Win.C1Preview.ZoomModeEnum.ActualSize;
            ppvViewer.NavigationPanelVisible = false;
            ppvViewer.Document = rptReport.Document;
            ppvViewer.Show();
        }
    }
}
